﻿  <DataListSpec
   xmlns="bb_appfx_datalist"
   xmlns:common="bb_appfx_commontypes"
   ID="b504e756-0e60-405c-8649-04a973c96986"
   Name="ProspectTeamsForConstituent (Deletions) Data List"
   Description="Returns Prospect Team List (deletions) by Constituent (ProspectID)"
   Author="Blackbaud Professional Services"
   common:SecurityUIFolder="Service Bus Adapter">



    <!-- define the SP that returns the data for the datalist -->
    <SPDataList SPName="USP_DATALIST_PROSPECTTEAMSFORCONSTITUENTDELETIONS">
      <common:CreateProcedureSQL>
        <![CDATA[
create procedure dbo.USP_DATALIST_PROSPECTTEAMSFORCONSTITUENTDELETIONS(
  @CONSTITUENTID uniqueidentifier, @UPDATEDSINCE datetime)
as
	set nocount on;

  SELECT
  cp.ID,
  cp.LOOKUPID as PROSPECTLOOKUPID,
  cm.LOOKUPID as MEMBERLOOKUPID,
  pt.DATEFROM, pt.DATETO,
  ptr.DESCRIPTION AS PROSPECTTEAMROLE,
  cp.DATEADDED,
  cp.DATECHANGED,
  ca.USERNAME as CHANGEDBYUSERNAME 
  

FROM PROSPECTTEAMAUDIT pt

INNER JOIN CONSTITUENT cp ON pt.PROSPECTID = cp.ID
  INNER JOIN CONSTITUENT cm ON pt.PROSPECTID = cm.ID
  INNER JOIN PROSPECTTEAMROLECODE ptr ON pt.PROSPECTTEAMROLECODEID = ptr.ID
  LEFT OUTER JOIN CHANGEAGENT     ca  ON ca.ID                = pt.CHANGEDBYID  

WHERE 
pt.AUDITTYPECODE = 2 --DELETE
AND pt.PROSPECTID = @CONSTITUENTID
AND pt.DATECHANGED >= @UPDATEDSINCE  


			]]>
      </common:CreateProcedureSQL>
    </SPDataList>

    <!-- describe any parameters (other than the context ID) defined on the SP -->
    <Parameters>
      <common:FormMetaData>
        <common:FormFields>
          <common:FormField FieldID="CONSTITUENTID" Caption="Constituent ID" DataType="Guid"/>
          <common:FormField FieldID="UPDATEDSINCE" Caption="Changed Since" DataType="Date"/>
        </common:FormFields>
      </common:FormMetaData>
    </Parameters>

    <!-- define the output fields in the list -->
    <Output>
      <OutputFields>
        <!-- 0 -->
        <OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" />
        <!-- 1 -->
        <OutputField FieldID="PROSPECTLOOKUPID" Caption="Prospect Lookup ID" DataType="String" />
        <!-- 2 -->
        <OutputField FieldID="MEMBERLOOKUPID" Caption="Member Lookup ID" DataType="String" />
        <!-- 3 -->
        <OutputField FieldID="DATEFROM" Caption="Date From" DataType="String" />
        <!-- 4 -->
        <OutputField FieldID="DATETO" Caption="Date To" DataType="String" />
        <!-- 5 -->
        <OutputField FieldID="PROSPECTTEAMROLE" Caption="Prospect Team Role" DataType="String" />
        <!-- 6 -->
        <OutputField FieldID="DATEADDED" Caption="Date Added" DataType="Date" />
        <!-- 7 -->
        <OutputField FieldID="DATECHANGED" Caption="Date Changed" DataType="Date" />
        <!-- 8 -->
        <OutputField FieldID="CHANGEDBYUSERNAME" Caption="Changed by User Name" DataType="String" />
      </OutputFields>
    </Output>
  </DataListSpec>
